'''
--  @owner:opentestcase005
--  @date:2020/11/9
--  @testpoint:修改参数vacuum_defer_cleanup_age，执行vacuum命令
'''
import sys
import unittest
sys.path.append(sys.path[0]+"/../")
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH


logger = Logger()
commonsh = CommonSH('dbuser')
constant = Constant()

class SYS_Operation(unittest.TestCase):
    def setUp(self):
        logger.info('------------------------Opengauss_Function_DML_Set_Case0120开始执行-----------------------------')

    def test_index(self):
        # 查看参数默认值并修改参数值
        sql_cmd1 = commonsh.execut_db_sql('''show vacuum_defer_cleanup_age;
        ALTER SYSTEM SET vacuum_defer_cleanup_age TO 1000000;''')
        logger.info(sql_cmd1)
        self.assertIn('0', sql_cmd1)
        self.assertIn(constant.ALTER_SYSTEM_SUCCESS_MSG, sql_cmd1)
        # 建表并插入数据；对表数据进行update操作,死行数为999
        sql_cmd2 = commonsh.execut_db_sql('''drop table if exists customer_info;
       CREATE TABLE customer_info(WR_RETURNED_DATE_SK INTEGER ,WR_RETURNED_TIME_name varchar(200) );
       insert into customer_info values (generate_series(1,2000),'a');
       update customer_info set WR_RETURNED_TIME_name='c' where WR_RETURNED_DATE_SK >1000 and WR_RETURNED_DATE_SK<2000;
       select n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_data_changed from pg_stat_all_tables where relname='customer_info';''')
        logger.info(sql_cmd2)
        self.assertIn(constant.CREATE_TABLE_SUCCESS, sql_cmd2)
        self.assertIn(constant.INSERT_SUCCESS_MSG, sql_cmd2)
        self.assertIn(constant.UPDATE_SUCCESS_MSG, sql_cmd2)
        self.assertIn('999', sql_cmd2)
        # 执行vacuum命令
        sql_cmd3 = commonsh.execut_db_sql('''vacuum customer_info;
       vacuum full customer_info;''')
        logger.info(sql_cmd3)
        self.assertIn(constant.VACUUM_SUCCESS_MSG, sql_cmd3)

    # 清理环境
    def tearDown(self):
        logger.info('----------this is teardown-------')
        # 恢复参数默认值并删除表
        sql_cmd3 = commonsh.execut_db_sql('''ALTER SYSTEM SET vacuum_defer_cleanup_age TO 0;
        drop table customer_info;''')
        logger.info(sql_cmd3)
        logger.info('------------------------Opengauss_Function_DML_Set_Case0120执行结束--------------------------')
